﻿Public Class perPropostas
    Inherits perAcessoBD

    Public Function inserirProposta(ByVal iCodProposta As Int32,
                               ByVal iRevProposta As Int32,
                               ByVal sCodCliente As String,
                               ByVal dtDataProposta As Date,
                               ByVal iSegmento As Int32,
                               ByVal sSolicitacao As String,
                               ByVal iValProposta As Int32,
                               ByVal sReferencia As String,
                               ByVal iModalidadeVenda As Int32,
                               ByVal iCondicaoVenda As Int32,
                               ByVal dtDataEntrega As Date,
                               ByVal bSemImposto As Boolean,
                               ByVal dEBTIDA_DespVenda As Double,
                               ByVal dEBTIDA_DespAdm As Double,
                               ByVal dEBTIDA_Depreciacao As Double,
                               ByVal dEvolCusto As Double,
                               ByVal dComissao As Double,
                               ByVal sCodFrete As String,
                               ByVal iTipoExp As Int32,
                               ByVal dValorFrete As Decimal,
                               ByVal dICMS As Double,
                               ByVal dPISCOFINS As Double,
                               ByVal iStatus As Int32,
                               ByVal iMercado As Int32,
                               ByVal dValorProposta As Decimal) As Int32
        Dim sSql As String
        Dim objProximoID As New ProximoID

        If iCodProposta = 0 Then
            iCodProposta = objProximoID.BuscaID("codProposta", "proposta")
        End If

        sSql = "INSERT INTO proposta" & Environment.NewLine &
                   "           (codProposta" & Environment.NewLine &
                   "           ,revProposta" & Environment.NewLine &
                   "           ,cliente" & Environment.NewLine &
                   "           ,aosCuidados" & Environment.NewLine &
                   "           ,dataProposta" & Environment.NewLine &
                   "           ,segmento" & Environment.NewLine &
                   "           ,diasValidade" & Environment.NewLine &
                   "           ,referencia" & Environment.NewLine &
                   "           ,modalidadeVenda" & Environment.NewLine &
                   "           ,condicaoVenda" & Environment.NewLine &
                   "           ,dataEntrega" & Environment.NewLine &
                   "           ,apresentarSemImposto" & Environment.NewLine &
                   "           ,EBTIDA_DespVendas" & Environment.NewLine &
                   "           ,EBTIDA_DespAdm" & Environment.NewLine &
                   "           ,EBTIDA_Depreciacao" & Environment.NewLine &
                   "           ,percEvolCusto" & Environment.NewLine &
                   "           ,percComissao" & Environment.NewLine &
                   "           ,CodFrete" & Environment.NewLine &
                   "           ,tipoExp" & Environment.NewLine &
                   "           ,valorFrete" & Environment.NewLine &
                   "           ,Icms" & Environment.NewLine &
                   "           ,PisCofins" & Environment.NewLine &
                   "           ,status" & Environment.NewLine &
                   "           ,mercado" & Environment.NewLine &
                   "           ,valorProposta " & Environment.NewLine &
                   "           )" & Environment.NewLine &
                   "        VALUES" & Environment.NewLine &
                   "           (@codProposta " & Environment.NewLine &
                   "           ,@revProposta" & Environment.NewLine &
                   "           ,@codCliente" & Environment.NewLine &
                   "           ,@aosCuidados" & Environment.NewLine &
                   "           ,@dataProposta" & Environment.NewLine &
                   "           ,@segmento" & Environment.NewLine &
                   "           ,@diasValidade" & Environment.NewLine &
                   "           ,@referencia" & Environment.NewLine &
                   "           ,@modalidadeVenda" & Environment.NewLine &
                   "           ,@condicaoVenda" & Environment.NewLine &
                   "           ,@dataEntrega" & Environment.NewLine &
                   "           ,@apresentarSemImposto" & Environment.NewLine &
                   "           ,@EBTIDA_despVendas" & Environment.NewLine &
                   "           ,@EBTIDA_despAdm" & Environment.NewLine &
                   "           ,@EBTIDA_depreciacao" & Environment.NewLine &
                   "           ,@percEvolCusto" & Environment.NewLine &
                   "           ,@percComissao" & Environment.NewLine &
                   "           ,@codFrete" & Environment.NewLine &
                   "           ,@tipoExp" & Environment.NewLine &
                   "           ,@valorFrete" & Environment.NewLine &
                   "           ,@Icms" & Environment.NewLine &
                   "           ,@PisCofins" & Environment.NewLine &
                   "           ,@Status " & Environment.NewLine &
                   "           ,@Mercado " & Environment.NewLine &
                   "           ,@valorProposta " & Environment.NewLine &
                   "           );Select IDENT_CURRENT('proposta') "

        With MyBase.SQLCmd.Parameters
            .Clear()
            .AddWithValue("codProposta", iCodProposta)
            .AddWithValue("revProposta", iRevProposta)
            .AddWithValue("codCliente", sCodCliente)
            .AddWithValue("aosCuidados", sSolicitacao)
            .AddWithValue("dataProposta", dtDataProposta)
            .AddWithValue("segmento", iSegmento)
            .AddWithValue("diasValidade", iValProposta)
            .AddWithValue("referencia", sReferencia)
            .AddWithValue("modalidadeVenda", iModalidadeVenda)
            .AddWithValue("condicaoVenda", iCondicaoVenda)
            .AddWithValue("dataEntrega", dtDataEntrega)
            .AddWithValue("apresentarSemImposto", bSemImposto)
            .AddWithValue("EBTIDA_despVendas", dEBTIDA_DespVenda)
            .AddWithValue("EBTIDA_despAdm", dEBTIDA_DespAdm)
            .AddWithValue("EBTIDA_depreciacao", dEBTIDA_Depreciacao)
            .AddWithValue("percEvolCusto", dEvolCusto)
            .AddWithValue("percComissao", dComissao)
            .AddWithValue("codFrete", IIf(IsNothing(sCodFrete), "", sCodFrete))
            .AddWithValue("tipoExp", iTipoExp)
            .AddWithValue("valorFrete", dValorFrete)
            .AddWithValue("Icms", dICMS)
            .AddWithValue("PisCofins", dPISCOFINS)
            .AddWithValue("status", iStatus)
            .AddWithValue("mercado", iMercado)
            .AddWithValue("valorProposta", dValorProposta)
        End With

        MyBase.executarAcao(sSql)

        Return iCodProposta

    End Function

    Public Function retornarPropostas(ByVal iCodProposta As Int32, ByVal iRevisao As Int32,
                                      ByVal dtInicioProposta As Date, ByVal dtFimProposta As Date,
                                      ByVal dtInicioValidade As Date, ByVal dtFimValidade As Date,
                                      ByVal sCodCliente As String, ByVal iStatus As Int32,
                                      ByVal dtAprovacao As Date) As DataTable
        Dim sSql As String

        sSql = "SELECT CONVERT(NVARCHAR(50),segmento)  + '-' + RIGHT('0000'+ CONVERT(VARCHAR,codProposta),5)  as Cotacao," & Environment.NewLine &
               "       codProposta," & Environment.NewLine &
               "       revProposta," & Environment.NewLine &
               "       Cliente.RazaoSocial as Cliente, " & Environment.NewLine &
               "       segmento," & Environment.NewLine &
               "	   dataProposta," & Environment.NewLine &
               "       DATEADD(d,diasValidade, dataProposta) as Validade," & Environment.NewLine &
               "       valorProposta" & Environment.NewLine &
               " FROM Proposta" & Environment.NewLine &
               "      LEFT JOIN cliente ON proposta.cliente = cliente.codCliente" & Environment.NewLine &
               " WHERE 1=1" & Environment.NewLine
        If iCodProposta > 0 Then
            sSql &= "  AND codProposta = @codProposta " & Environment.NewLine
        End If

        If iRevisao >= 0 Then
            sSql &= "   AND revProposta = @revProposta " & Environment.NewLine
        End If

        If dtInicioProposta <> Date.MinValue Then
            sSql &= "   AND proposta.dataProposta >= @inicioProposta" & Environment.NewLine
        End If

        If dtFimProposta <> Date.MinValue Then
            sSql &= "   AND proposta.dataProposta <= @fimProposta" & Environment.NewLine
        End If

        If dtInicioValidade <> Date.MinValue Then
            sSql &= "   AND DATEADD(d,diasValidade, dataProposta)>= @inicioValidade" & Environment.NewLine
        End If

        If dtFimValidade <> Date.MinValue Then
            sSql &= "   AND DATEADD(d,diasValidade, dataProposta)<= @fimValidade" & Environment.NewLine
        End If

        If sCodCliente <> String.Empty Then
            sSql &= "   AND cliente = @cliente" & Environment.NewLine
        End If

        If iStatus <> -1 Then
            sSql &= "   AND status = @status" & Environment.NewLine
        End If

        sSql &= "ORDER BY codProposta desc, revProposta desc, dataProposta DESC"

        With MyBase.SQLCmd.Parameters
            .Clear()

            If iCodProposta > 0 Then
                .AddWithValue("@codProposta", iCodProposta)
            End If

            If sCodCliente <> String.Empty Then
                .AddWithValue("@codCliente", sCodCliente)
            End If

            If iRevisao >= 0 Then
                .AddWithValue("@revProposta", iRevisao)
            End If

            If dtInicioProposta <> Date.MinValue Then
                .AddWithValue("@inicioProposta", dtInicioProposta)
            End If

            If dtFimProposta <> Date.MinValue Then
                .AddWithValue("@fimProposta", dtFimProposta)
            End If

            If dtInicioValidade <> Date.MinValue Then
                .AddWithValue("@inicioValidade", dtInicioValidade)
            End If

            If dtFimValidade <> Date.MinValue Then
                .AddWithValue("@fimValidade", dtFimValidade)
            End If

            If sCodCliente <> String.Empty Then
                .AddWithValue("@cliente", sCodCliente)
            End If

            If iStatus <> -1 Then
                .AddWithValue("@status", iStatus)
            End If

            Return MyBase.executarConsulta(sSql)

        End With

    End Function

    Public Function retornarPropostaDetalhes(ByVal icodProposta As Int32, ByVal iRevProposta As Int32) As DataRow
        Dim sSql As String
        Dim dtbRetorno As DataTable

        sSql = "SELECT Proposta.codProposta, " & Environment.NewLine &
               "       Proposta.revProposta, " & Environment.NewLine &
               "       Proposta.dataProposta," & Environment.NewLine &
               "       Proposta.cliente," & Environment.NewLine &
               "        cliente.razaoSocial," & Environment.NewLine &
               "        Proposta.aosCuidados," & Environment.NewLine &
               "        Proposta.diasValidade," & Environment.NewLine &
               "        Proposta.referencia," & Environment.NewLine &
               "        Proposta.modalidadeVenda," & Environment.NewLine &
               "        Proposta.condicaoVenda," & Environment.NewLine &
               "        Proposta.dataEntrega," & Environment.NewLine &
               "        Proposta.apresentarSemImposto," & Environment.NewLine &
               "        Proposta.EBTIDA_despVendas," & Environment.NewLine &
               "        Proposta.EBTIDA_despAdm," & Environment.NewLine &
               "        Proposta.EBTIDA_depreciacao," & Environment.NewLine &
               "        Proposta.percEvolCusto," & Environment.NewLine &
               "        Proposta.percComissao," & Environment.NewLine &
               "        Proposta.codFrete," & Environment.NewLine &
               "        Frete.descItinerario," & Environment.NewLine &
               "        Proposta.tipoExp," & Environment.NewLine &
               "        Frete.tipoVeiculo," & Environment.NewLine &
               "        Proposta.valorFrete," & Environment.NewLine &
               "        Proposta.ICMS," & Environment.NewLine &
               "        Proposta.PisCofins," & Environment.NewLine &
               "        Proposta.Status," & Environment.NewLine &
               "        Proposta.valorProposta" & Environment.NewLine &
               "   FROM Proposta" & Environment.NewLine &
               "        INNER JOIN Cliente on Proposta.cliente = Cliente.codCliente" & Environment.NewLine &
               "        LEFT JOIN Frete on Proposta.codFrete = Frete.codFrete" & Environment.NewLine &
               "                         AND Proposta.tipoExp = Frete.tipoExp" & Environment.NewLine &
               "  WHERE Proposta.codProposta = @codProposta" & Environment.NewLine &
               "    AND Proposta.revProposta = @revProposta"

        With SQLCmd.Parameters
            .Clear()
            .AddWithValue("codProposta", icodProposta)
            .AddWithValue("revProposta", iRevProposta)
        End With

        dtbRetorno = MyBase.executarConsulta(sSql)
        If dtbRetorno.Rows.Count > 0 Then
            Return dtbRetorno.Rows(0)
        Else
            Return Nothing
        End If

    End Function

    Public Function retornarRevProposta(ByVal icodProposta As Int32) As Int32
        Dim sSql As String

        sSql = "SELECT MAX(revProposta) +1 " & Environment.NewLine &
               "  FROM proposta" & Environment.NewLine &
               " WHERE codProposta = @codProposta "

        With MyBase.SQLCmd.Parameters
            .Clear()
            .AddWithValue("@codProposta", icodProposta)
        End With

        Return Conversao.ToInt32(MyBase.executarConsultaCampo(sSql))

    End Function

End Class
